package com.micromall.service.util;

import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;


@Component
public class JdbcServiceImpl {

	private static final Logger logger = LoggerFactory.getLogger(JdbcServiceImpl.class);

    public final static int SQL_DEFAULT_PAGE_SIZE = 1000;
	
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	/**
	 * 根据sql执行查询
	 * @param sql
	 * @return
	 * @author Creation Fountain.Chan
	 * @author Last Fountain.Chan
	 * creation Date : 2012-6-5 下午5:53:42
	 * last Modified : 2012-6-5 下午5:53:42
	 */
	protected List<?> queryForList( String sql ){
		return getJdbcTemplate().queryForList( sql );
	}
	
	/**
	 * 执行sql
	 * @param sql
	 * @author Creation Fountain.Chan
	 * @author Last Fountain.Chan
	 * creation Date : 2012-6-5 下午5:54:05
	 * last Modified : 2012-6-5 下午5:54:05
	 */
	protected boolean excuteSql( String sql ){
		boolean isSuccess = false;
		try{
			getJdbcTemplate().execute( sql );	
		}catch(DataAccessException dataAccessException){
			isSuccess = false;
		}
		return isSuccess;
	}
	
	/**
	 * 分页sql查询
	 * @param sql
	 * @return
	 * @author Creation Fountain.Chan
	 * @author Last Fountain.Chan
	 * creation Date : 2012-6-5 下午5:54:12
	 * last Modified : 2012-6-5 下午5:54:12
	 */
	public List<?> queryForListSkip( String sql ){
		return this.queryForList( this.generateSkipSql( sql ) );
	}
	
	/**
	 * 获取列表
	 * @param sql
	 * @param pageno
	 * @param pageSize
	 * @return
	 * @author Creation Fountain.Chan
	 * @author Last Fountain.Chan
	 * creation Date : 2012-6-6 上午9:46:40
	 * last Modified : 2012-6-6 上午9:46:40
	 */
	public List<?> queryForListPage( String sql, int pageno, int pageSize ){
		return getJdbcTemplate().queryForList( this.generateSkipSql(sql, pageno, pageSize) );
	}
	
	/**
	 * 生成sql分页语句
	 * @param sql
	 * @return
	 * @author Creation Fountain.Chan
	 * @author Last Fountain.Chan
	 * creation Date : 2012-6-5 下午5:54:25
	 * last Modified : 2012-6-5 下午5:54:25
	 */
	private String generateSkipSql( String sql ){
		return this.generateSkipSql(sql, -1, -1);
	}
	
	
	/**
	 * 生成sql分页语句
	 * @param sql
	 * @param pageNo
	 * @param pageSize
	 * @return
	 * @author Creation Fountain.Chan
	 * @author Last Fountain.Chan
	 * creation Date : 2012-6-5 下午5:56:28
	 * last Modified : 2012-6-5 下午5:56:28
	 */
	@Deprecated
	private String generateSkipSqlOld( String sql, int pageNo, int pageSize ){
		StringBuffer sb = new StringBuffer();
		Integer defaultPageSize = SQL_DEFAULT_PAGE_SIZE;
		if( pageSize >= 0 ) defaultPageSize = pageSize;
		else if( pageSize == -1 ) defaultPageSize = Integer.MAX_VALUE - 100;
		String andClaue = "";
		if( pageNo >= 0 )	andClaue = " and rownum >= " + pageNo;
		sb
		.append( "select * from (" )
		.append( "	select ROWNUM row_index, t.* from (" )
		.append( sql )
		.append( "	) t ")
		.append( " where rownum <= " + defaultPageSize)
		.append(andClaue)
		.append( ")" );
		logger.info(sb.toString());
		return sb.toString();
	}

	/**
	 * 生成sql分页语句
	 * eg.
	 * select * from
			 (
			 select ROWNUM row_index, t.*
			 from (
			 select  po.user_code  ,po.order_no  ,ps.ship_last_name  ,ps.ship_id  ,ps.ship_address1  ,ps.ship_phone  ,po.total_amount  ,po.create_time  ,po.status
			 from po_order po, po_ship ps where 1=1  and po.order_id = ps.order_id and po.status = '20'
			 ) t  where rownum <= 4 // (pageNo-1)*pageSize + pageSize
		)where row_index > 2;//  (pageNo-1)*pageSize
	 * @param sql
	 * @param pageNo
	 * @param pageSize
	 * @return
	 * @author Creation DanteFung
	 * @author Last DanteFung
	 * creation Date : 2017-7-11 16:05:40
	 * last Modified : 2017-7-11 16:05:45
	 */
	private String generateSkipSql( String sql, int pageNo, int pageSize ){
		StringBuffer sb = new StringBuffer();
		Integer defaultPageSize = SQL_DEFAULT_PAGE_SIZE;
		if( pageSize >= 0 ) defaultPageSize = (pageNo-1)*pageSize + pageSize;
		else if( pageSize == -1 ) defaultPageSize = Integer.MAX_VALUE - 100;
		String whereClause = "";
		if( pageNo >= 0 )	whereClause = " where row_index > " + (pageNo-1)*pageSize;
		sb
		.append( "select * from (" )
		.append( "	select ROWNUM row_index, t.* from (" )
		.append( sql )
		.append( "	) t ")
		.append( " where rownum <= " + defaultPageSize)
		.append( ")" )
		.append( whereClause );
		logger.info(sb.toString());
		return sb.toString();
	}

	/**
	 * @return the jdbcTemplate
	 */
	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	/**
	 * @param jdbcTemplate the jdbcTemplate to set
	 */
	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
	
	
}